﻿package edu.pku.se.chocolate.solver;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Random;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;
import edu.pku.se.chocolate.R;
import edu.pku.se.chocolate.datastructure.Category;
import edu.pku.se.chocolate.datastructure.Message;
import edu.pku.se.chocolate.datastructure.Tag;

/**
 * Storage
 * 存储...
 * @author Jas0n
 *
 */
public class Storage {
	public static int  DB_VERSION = 22;  
	
	public Storage(ChocolateSolver solver){
		DBOpenHelper dbOpenHelper = new DBOpenHelper(solver.activity, DB_NAME,solver.getCategoryList());		
		db = dbOpenHelper.getWritableDatabase();
		bbs_db = GetBBSDB(solver.activity);
	}
	public static final String PACKAGE_NAME = "edu.pku.se.chocolate";
	public static final String DB_PATH = "/data"
					 + Environment.getDataDirectory().getAbsolutePath() + "/"
					 + PACKAGE_NAME+"/databases";
	private SQLiteDatabase GetBBSDB(Activity activity) {
//		InputStream is = activity.getResources().openRawResource(R.raw.bbsinfo);
		String outFileName = DB_PATH + "/"+ BBS_DB_NAME;

        //检测是否已经创建
        File dir = new File(outFileName);
        if(dir.exists())
            return SQLiteDatabase.openOrCreateDatabase(outFileName,null);

        // 检测/创建数据库的文件夹
        dir = new File(DB_PATH);

        if (!dir.exists())
            dir.mkdir();

        InputStream input = null;
        OutputStream output = null;
        // 从资源中读取数据库流
        input = activity.getResources().openRawResource(R.raw.bbsinfo);

        try {
            output = new FileOutputStream(outFileName);

            // 拷贝到输出流
            byte[] buffer = new byte[2048];
            int length;
            while ((length = input.read(buffer)) > 0) {
                output.write(buffer, 0, length);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭输出流
            try {
                output.flush();
                output.close();
            } catch (IOException e) {
            }
            // 关闭输入流
            try {
                input.close();
            } catch (IOException e) {
            }
        }
        return SQLiteDatabase.openOrCreateDatabase(outFileName,null);
	}
	public ArrayList<Tag> getTagList(String ctgname) {
		Log.v("Storage.getTagList", "ctgname:"+ctgname);
		Cursor c = db.query(T_Tag, null,C_Tctg +"='"+ctgname+"'",null, null, null, null);
		return convertCursorToTagList(c);
	}
	public ArrayList<Tag> getAllNotifyTag() {
		Cursor c = db.query(T_Tag, null,C_Tisnotify +"='"+1+"'",null, null, null, null);
		return convertCursorToTagList(c);		
	}
	private ArrayList<Tag> convertCursorToTagList(Cursor c) {
		ArrayList<Tag> list = new ArrayList<Tag>(c.getCount());
		if (c.moveToFirst()){
			do{
				Tag tag = new Tag(
					c.getInt(c.getColumnIndex(C_Ttid)),
					c.getString(c.getColumnIndex(C_Tctg)).trim(),
					c.getString(c.getColumnIndex(C_Tname)).trim(),
					c.getString(c.getColumnIndex(C_Tsource)).trim(),
					c.getString(c.getColumnIndex(C_TkeyWord)).trim(),
					c.getInt(c.getColumnIndex(C_Ttype)),
					c.getInt(c.getColumnIndex(C_Tnewmsgnum)),
					c.getInt(c.getColumnIndex(C_TlatestestMID)),
					c.getInt(c.getColumnIndex(C_Tisnotify))
					);
				list.add(tag);
			} while (c.moveToNext());
		}
		c.close();
		return list; 
	}
	public ArrayList<Message> getLatestMessages(int tiD, int latest_msg_id, int pageItemNum) {
		String sql = "select " + T_Msg + ".* from " + T_Msg + "," + T_Rmt 
					+ " where "+C_Rtid + " = " + tiD  
//					+ " and " + C_Rmid + " > " + latest_msg_id
					+ " and " + C_Rmid + " = " + C_Mmid
					+ " order by " + C_Mmid +" desc "
					+ " limit "+pageItemNum;			
		Cursor c = db.rawQuery(sql, null);
		return convertCursorToMessageList(c);
	}

	public ArrayList<Message> getNextNMessage(int tiD, int pre_id, int num) {
		String sql = "select " + T_Msg + ".* from " + T_Msg + "," + T_Rmt 
				+ " where "+C_Rtid + " = " + tiD  
				+ " and " + C_Rmid + " < " + pre_id
				+ " and " + C_Rmid + " = " + C_Mmid
				+ " order by " + C_Mmid + " desc "
				+ " limit " + num;
		Cursor c = db.rawQuery(sql, null);
		return convertCursorToMessageList(c);
	}

	private ArrayList<Message> convertCursorToMessageList(Cursor c) {
		ArrayList<Message> list = new ArrayList<Message>(c.getCount());
		if (c.moveToFirst()){
		do{
			Message msg = new Message(
					c.getInt(c.getColumnIndex(C_Mmid)),
					c.getString(c.getColumnIndex(C_Msummary)).trim(),
					c.getString(c.getColumnIndex(C_Mlink)).trim(),
					c.getString(c.getColumnIndex(C_Mtime)).trim(),
					c.getString(c.getColumnIndex(C_Mtitle)).trim(),
					c.getLong(c.getColumnIndex(C_Mfavorite)),
					c.getInt(c.getColumnIndex(C_Misread))
			);
			list.add(msg);
		} while (c.moveToNext());
		}
		c.close();
		return list;
	}
	public int getNewMessageNumber(int iD) {
		Cursor c = db.query(T_Tag, new String[]{C_Tnewmsgnum}, 
				C_Ttid+"="+iD, null, null, null, null);
		if (c.getCount() > 0 && c.moveToFirst()){
			return c.getInt(0);
		}
		c.close();
		return -1;
	}

	public boolean addTag(Tag tag) {
		ContentValues cv = new ContentValues();
		//cv.put(C_Ttid, tag.ID);
		cv.put(C_Tctg, tag.catagory);
		cv.put(C_Tname, tag.name);
		cv.put(C_Tsource, tag.source);
		cv.put(C_TkeyWord, tag.keyword);
		cv.put(C_Ttype, tag.type);
		cv.put(C_Tnewmsgnum, tag.new_msg_num);
		cv.put(C_TlatestestMID, tag.latest_msg_id);
		cv.put(C_Tisnotify, tag.isNotify);
		try {
			db.insertOrThrow(T_Tag, null, cv);
		} catch (SQLException e){
			return false;
		}
		return true;
	}

	public boolean deleteTag(Tag tag) {
		int cnt1 = db.delete(T_Tag, C_Ttid+"="+tag.ID, null);
		int cnt2 = db.delete(T_Rmt, C_Rtid+"="+tag.ID, null);
		Log.v("Storage.deleteTag","delete "+ cnt1 + "tag and "+cnt2 + "relation");
		return cnt1 > 0;
	}
	
	public boolean collectMessage(int iD) {
		ContentValues cv = new ContentValues();
		cv.put(C_Mfavorite, System.currentTimeMillis());
		return 0 < db.update(T_Msg, cv, C_Mmid+"="+iD, null);
	}

	public boolean cancleCollectMessage(int iD) {
		ContentValues cv = new ContentValues();
		cv.put(C_Mfavorite, 0);
		return 0 < db.update(T_Msg, cv, C_Mmid+"="+iD,null);		
	}
	public ArrayList<Message> getFavouriteMessage(int i, int pageItemNum) {
//		Cursor c = db.query(T_Msg, null , C_Mfavorite+">0", null, 
//						null, null, C_Mfavorite + " desc");
//						//"limit " + pageItemNum + " offset "+ i);		
		String sql = "select * from " + T_Msg
					+ " where " + C_Mfavorite + " > 0 "
					+ " order by " + C_Mfavorite + " desc "
					+ " limit " + i + "," + pageItemNum;
		Cursor c = db.rawQuery(sql, null);
		return convertCursorToMessageList(c);
	}
	public ArrayList<String> getBBSforumName(String preffix) {
		Cursor c = bbs_db.query(T_Bbs, null, 
				"substr(lower("+C_Beng+"),1,"+preffix.length()+")='"+preffix+"' "
				+"or substr(lower("+C_Bchs+"),1,"+preffix.length()+")='"+preffix+"' ",
				null, null, null, null,"10");
		ArrayList<String> s = new ArrayList<String>(c.getCount());
		if (c.moveToFirst()){
			do{
				s.add(c.getString(c.getColumnIndex(C_Beng)).trim()
						+"/"+c.getString(c.getColumnIndex(C_Bchs)).trim());
			}while (c.moveToNext());
		}
		c.close();
		return s;
	}
	public void addMessage(Tag tag, ArrayList<Message> messages) {
		ContentValues cv = new ContentValues();
		for(Message msg:messages){
			try{
				cv.clear();
				cv.put(C_Mmid,msg.ID);
				cv.put(C_Mfavorite, 0);
				cv.put(C_Misread, 0);
				cv.put(C_Mlink, msg.link);
				cv.put(C_Msummary, msg.summary);
				cv.put(C_Mtime, msg.time);
				cv.put(C_Mtitle, msg.title);
				db.insertOrThrow(T_Msg, null, cv);				
			} catch (Exception e) {
				Log.v("Storage.addMessage","repeat Message,ignore");
			}
			try{
				cv.clear();
				cv.put(C_Rmid,msg.ID);
				cv.put(C_Rtid,tag.ID);
				db.insertOrThrow(T_Rmt, null, cv);
			} catch (Exception e) {
				Log.v("Storage.addMessage","relation repeat, ignore");
			}
		}
	}
//	public void updateTag(Tag tag, int newestID, int newMessageNumber) {
//		ContentValues cv = new ContentValues();
//		cv.put(C_Tnewmsgnum, newMessageNumber);
//		cv.put(C_TlatestestMID, newestID);
//		db.update(T_Tag, cv, C_Ttid+"="+tag.ID, null);
//	}
	public boolean updateMessage(int id,String[] cols,String[] newValues){
		ContentValues cv = new ContentValues();
		for(int i = 0 ; i < cols.length; i++)
			cv.put(cols[i], newValues[i]);
		return 0 < db.update(T_Msg, cv, C_Mmid+"="+id, null);
	}
	public boolean updateTag(int id,String[] cols,String[] newValues){
		ContentValues cv = new ContentValues();
		for(int i = 0 ; i < cols.length; i++)
			cv.put(cols[i], newValues[i]);
		return 0 < db.update(T_Tag, cv, C_Ttid+"="+id, null);
	}
	public Tag getTag(int id) {
		Cursor c = db.query(T_Tag, null, C_Ttid+"="+id, null, null, null, null);
		ArrayList<Tag> taglist = convertCursorToTagList(c);
		if (taglist.size()!= 1){
			Log.e("Storage.getTag","num error");
			return null;
		} else return taglist.get(0);
	}
	public ArrayList<String> getBBSAllforumName() {
		Cursor c = bbs_db.query(T_Bbs, null, null, null, null, null,null);
		ArrayList<String> s = new ArrayList<String>(c.getCount());
		if (c.moveToFirst()){
			do{
				s.add(c.getString(c.getColumnIndex(C_Beng)).trim()
						+"/"+c.getString(c.getColumnIndex(C_Bchs)).trim());
			}while (c.moveToNext());
		}
		c.close();
		return s;
	}
	
	/**
	 * BBS data
	 */
	private SQLiteDatabase bbs_db;
	private static final String BBS_DB_NAME = "bbsinfoV2.db";
	public static final String T_Bbs = "bbsinfo";
	public static final String C_Beng = "eng";
	public static final String C_Bchs = "chs";

	/*
	 * -------------------------------------------------
	 * data base table and column
	 */
	private SQLiteDatabase db;
	private static final String DB_NAME = "chocolate.db";
	
	public static final String T_Msg = "Message";
	public static final String T_Tag = "Tag";
	public static final String T_Rmt = "RelationMsgTag";
	
	public static final String C_Mmid = "MID";
	public static final String C_Msummary = "summary";
	public static final String C_Mtitle = "title";
	public static final String C_Mlink = "link";
	public static final String C_Mtime = "time";
	public static final String C_Mfavorite = "favourite";
	public static final String C_Misread = "isRead";
	
	public static final String C_Ttid= "TID";
	public static final String C_Tctg = "category";
	public static final String C_Tname= "name";
	public static final String C_Tsource = "source";
	public static final String C_TkeyWord = "keyWord";
	public static final String C_Ttype = "type";
	public static final String C_Tnewmsgnum = "newMsgNum";
	public static final String C_TlatestestMID = "latestestMID";
	public static final String C_Tisnotify = "isNotify";
	
	public static final String C_Rmid = "RMID";
	public static final String C_Rtid = "RTID";
	
	public static final String Create_Table_Message = " create table "+T_Msg+"("
		+ C_Mmid + " INTEGER primary key,"
		+ C_Msummary + " TEXT,"
		+ C_Mtitle + " TEXT,"
		+ C_Mlink + " TEXT,"
		+ C_Mtime + " TEXT,"
		+ C_Mfavorite + " INTEGER,"
		+ C_Misread + " INTEGER);";
	public static final String Create_Table_Tag  = " create table "+T_Tag+"("
		+ C_Ttid + " INTEGER primary key autoincrement,"
		+ C_Tctg+ " TEXT,"
		+ C_Tname + " TEXT,"
		+ C_Tsource + " TEXT,"
		+ C_TkeyWord + " TEXT,"
		+ C_Ttype + " INTEGER,"
		+ C_Tnewmsgnum + " INTEGER,"
		+ C_TlatestestMID + " INTEGER,"
		+ C_Tisnotify + " INTEGER)";
	public static final String Create_Table_RelationMsgTag =  " create table "+T_Rmt+"("
		+ C_Rmid + " INTEGER ,"
		+ C_Rtid + " INTEGER ,"
		+ "Primary key("+C_Rmid+","+C_Rtid+"),"
		+ "FOREIGN KEY("+C_Rmid + ") REFERENCES "+T_Msg + "("+C_Mmid+"),"
		+ "FOREIGN KEY("+C_Rtid + ") REFERENCES "+T_Tag + "("+C_Ttid+"));";
	/**
	 * 
	 * @author Jas0n
	 *	DataBase....
	 */
	private static class DBOpenHelper extends SQLiteOpenHelper {
		
		ArrayList<Category> ctgList;
		public DBOpenHelper(Context context, String name,  ArrayList<Category> ctgList) {
			super(context, name, null, DB_VERSION);
			this.ctgList = ctgList;
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			//create table
			db.execSQL(Create_Table_Message);
			db.execSQL(Create_Table_Tag);
			db.execSQL(Create_Table_RelationMsgTag);
			initDB(db);
			//initDBwithRadomData(db);			
		}
		/**
		 * 正常初始化数据库
		 */		
		void initDB(SQLiteDatabase db){
			db.beginTransaction();
			try{
				//create "全部" tags
				ContentValues cv = new ContentValues(9);
				cv.put(C_Tname,"全部");				
				cv.put(C_TkeyWord, "");
				cv.put(C_Tnewmsgnum, 0);
				cv.put(C_TlatestestMID, -1);
				cv.put(C_Tisnotify,0);
				cv.put(C_Ttype,1);
				for(Category ctg : ctgList)
				if (!ctg.getStorageName().equals("custom")){
					ContentValues c = new ContentValues(cv);
					c.put(C_Tctg,ctg.getStorageName());
					c.put(C_Tsource,ctg.getStorageName());					
					db.insert(T_Tag, null, c);
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
		/**
		 * 为数据库添加随机数据.
		 */
		void initDBwithRadomData(SQLiteDatabase db){
			Log.v("initDBwithRadomData", "start");
			Random rnd = new Random();
			//加标签
			Log.v("initDBwithRadomData", "add tag");
			db.beginTransaction();
			try{
				ContentValues cv = new ContentValues(9);
				cv.put(C_Tname,"全部");				
				cv.put(C_TkeyWord, "全部");
				cv.put(C_Tnewmsgnum, 0);
				cv.put(C_TlatestestMID, -1);
				cv.put(C_Tisnotify,0);
				cv.put(C_Ttype,1);	
				ContentValues cv_rnd = new ContentValues(9);
				cv_rnd.put(C_TkeyWord, "keyword");
				cv_rnd.put(C_Tnewmsgnum, 0);
				cv_rnd.put(C_TlatestestMID, -1);
				cv_rnd.put(C_Tisnotify,1);
				cv_rnd.put(C_Ttype,1);
				for(Category ctg : ctgList){
					//Log.v("initDBwithRadomData","add Tag "+ctg.name);
					String name = ctg.getStorageName();
					if (!name.equals("custom")){
						ContentValues c = new ContentValues(cv);
						c.put(C_Tctg,name);
						c.put(C_Tsource,name);					
						db.insert(T_Tag, null, c);
						ContentValues cc = new ContentValues(cv_rnd);
						cc.put(C_Tname, "TAG"+rnd.nextInt(100));
						cc.put(C_Tctg,name);
						cc.put(C_Tsource,name);					
						db.insert(T_Tag, null, cc);
					} else {
						ContentValues c1 = new ContentValues(cv);
						c1.put(C_Tname,"T");	
						c1.put(C_Tctg,name);
						c1.put(C_Tsource,"Test/测试用版");	
						c1.put(C_TkeyWord, "test");
						c1.put(C_Tnewmsgnum, 0);
						c1.put(C_TlatestestMID, -1);
						c1.put(C_Tisnotify,1);
						c1.put(C_Ttype,3);
						db.insert(T_Tag, null, c1);
					}
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
			Log.v("initDBwithRadomData", "add msg");
			//加400消息
			db.beginTransaction();
			try{
				ContentValues cv = new ContentValues(7);
				long currtime = System.currentTimeMillis();
				for(int i = 0 ; i < 400; i++){									
					cv.clear();
					cv.put(C_Mmid,1001+i);
					cv.put(C_Mfavorite, 0);
					cv.put(C_Misread, 0);
					cv.put(C_Mlink, "http://www.pku.edu.cn");
					cv.put(C_Msummary, "Summary:ID-->"+(1001+i));
					cv.put(C_Mtime, new Date(rnd.nextLong()%currtime).toGMTString());
					cv.put(C_Mtitle, "title NO."+rnd.nextInt(10000));
					try{
						db.insert(T_Msg, null, cv);
					} catch (Exception e) {
					}
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
			//链接消息和标签
			ArrayList<Integer> tidList = new ArrayList<Integer>();
			Cursor cursor = db.query(T_Tag, new String[]{C_Ttid}, C_Tctg+"<>'custom'", null, null, null, null);
			if (cursor.moveToFirst()){
				do{
					tidList.add(cursor.getInt(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
			ArrayList<Integer> midList = new ArrayList<Integer>();
			cursor = db.query(T_Msg, new String[]{C_Mmid}, null, null, null, null, null);
			if (cursor.moveToFirst()){
				do{
					midList.add(cursor.getInt(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
			Log.v("initDBwithRadomData", "add relation");
			db.beginTransaction();
			try{
				ContentValues cv = new ContentValues(7);
				
				for(int tid : tidList){
					for(int i = 0 ; i < 40; i++){
						cv.clear();
						cv.put(C_Rmid,midList.get(rnd.nextInt(midList.size())));
						cv.put(C_Rtid,tid);
						try{
							db.insertOrThrow(T_Rmt, null, cv);
						} catch (Exception e) {
						}
					}
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
			_db.execSQL("DROP TABLE IF EXISTS " + T_Rmt);
			_db.execSQL("DROP TABLE IF EXISTS " + T_Tag);
			_db.execSQL("DROP TABLE IF EXISTS " + T_Msg);
			onCreate(_db);
		}
	}
//	public void setTagIsNotify(int iD, boolean isNotify) {
//		db.update(C_T, values, whereClause, whereArgs)
//		
//	}

	
}
